SYSFILES view

The SYSFILES view contains information about database files. Additional information is available in the QSYS2.SYSTABLES view.

The information returned is similar to the detail seen from the Display File Description (DSPFD) command and the Retrieve Database File Description (QDBRTVFD) API.

Authorization: The caller must have:

*EXECUTE authority to the library containing the file, and
*OBJOPR authority to the file.

The following table describes the columns in the view. The schema is QSYS2.

Table 1. SYSFILES view
Column Name	System Column Name	Data Type	Description
SYSTEM_TABLE_SCHEMA	LIB_NAME	VARCHAR(10)	The library containing the file.
SYSTEM_TABLE_NAME	FILE_NAME	VARCHAR(10)	The name of the file.
TABLE_SCHEMA	TABSCHEMA	VARCHAR(128)	The SQL schema name of the library.
TABLE_NAME	TABNAME	VARCHAR(128)	The SQL name of the file.
IASP_NUMBER	IASPNUMBER	INTEGER	The independent auxiliary storage pool (IASP) number.
TEXT_DESCRIPTION	TEXT	VARGRAPHIC(50) CCSID 1200 Nullable	The descriptive text for the file. Contains the null value if there is no descriptive text.
NATIVE_TYPE	DDS_TYPE	VARCHAR(8)	Type of file. LOGICAL This is a DDS created logical file or an SQL view or index. PHYSICAL This is a physical file or an SQL table.
FILE_TYPE	FILETYPE	VARCHAR(6)	Type of file. DATA This is a data file. SOURCE This is a source physical file.
SQL_OBJECT_TYPE	SQL_TYPE	VARCHAR(5) Nullable	SQL object type. INDEX This is an SQL index. TABLE This is an SQL table. VIEW This is an SQL view. Contains the null value if this is not an SQL object.
LAST_ALTERED_TIMESTAMP	ALTEREDTS	TIMESTAMP(0)	Timestamp when the file was last altered or created.
FILE_LEVEL_ID	FILE_LVLID	CHAR(13)	File level identifier. This is the date of the file in CYYMMDDHHMMSS format.
LEVEL_CHECK	LVLCHK	VARCHAR(3)	Record format level check (LVLCHK). NO The record format level identifiers are not checked when the file is opened. YES The record format level identifiers are checked when the file is opened. See Level checking for some additional information about how level checking works.
FILE_OWNER	OWNER	VARCHAR(10) Nullable	The owner of the object. Contains the null value if the object owner is not available.
CREATE_PUBLIC_AUTHORITY	PUB_AUTH	VARCHAR(10)	The public authority that the file was created with (AUT). This is not the current public authority for the file. ALL Public all authority CHANGE Public change authority EXCLUDE Public exclude authority USE Public use authority authorization-list-name The name of the authorization list whose authority is used for the file.
NUMBER_MEMBERS	MEMBERS	INTEGER	Number of members.
MAXIMUM_ MEMBERS	MAXMBRS	INTEGER Nullable	Maximum members (MAXMBRS). 1 to 32767 The maximum number of members for the file. Contains the null value if no maximum is specified; 32767 is used (*NOMAX).
MAXIMUM_RECORD_LENGTH	RECLENGTH	INTEGER	Maximum record length. This is the length of the record in the file's record format that contains the largest number of bytes.
NUMBER_KEY_FIELDS	KEY_FLDS	INTEGER	Number of key fields for the file.
MAXIMUM_KEY_LENGTH	KEY_LEN	INTEGER	Maximum key length for the file.
TRIGGER_COUNT	TRIG_CNT	INTEGER	Number of triggers.
CONSTRAINT_COUNT	CONSTR_CNT	INTEGER Nullable	Number of constraints for the file. Contains the null value if this is not a physical file.
NUMBER_BASED_ON_FILES	BASE_FILES	INTEGER Nullable	Number of files directly referenced by a logical file, view, or index. Contains the null value for physical files.
BASED_ON_FILES	BASED_ON	CLOB(35K) CCSID 1208 Nullable	A list of files directly referenced by this logical file, view, or index. This list is returned as an array within a JSON object. The array is identified by BASED_ON_FILES. Each entry in the JSON array identifies one file. Each array entry can contain information with the following keys: LIBRARY FILE MEMBER LF_FORMAT Contains the null value for physical files.
ALLOW_READ	ALWREAD	VARCHAR(3)	Allow read operation. NO Records are not allowed to be read from the file. YES Records are allowed to be read from the file.
ALLOW_WRITE	ALWWRITE	VARCHAR(3)	Allow write operation. NO Records are not allowed to be written to the file. YES Records are allowed to be written to the file.
ALLOW_UPDATE	ALWUPD	VARCHAR(3)	Allow update operation (ALWUPD). NO Records are not allowed to be updated in the file. YES Records are allowed to be updated in the file.
ALLOW_DELETE	ALWDLT	VARCHAR(3)	Allow delete operation (ALWDLT). NO Records are not allowed to be deleted from the file. YES Records are allowed to be deleted from the file.
MAXIMUM_FILE_WAIT_TIME	WAITFILE	INTEGER	Maximum file wait time (WAITFILE). -1 The default wait time specified in the class description is used (CLS). 0 The program does not wait for the file; an immediate allocation is required (IMMED). 1 through 32767 The maximum number of seconds a program waits for the file.
MAXIMUM_RECORD_WAIT_TIME	WAITRCD	INTEGER	Maximum record wait time (WAITRCD). -2 The default wait time allowed by the system is used. This is 32767 seconds (NOMAX). -1 The program does not wait for the record, an immediate allocation is required (IMMED). 1 through 32767 The maximum number of seconds a program waits for the record.
FORCE_WRITE_RATIO	FRCRATIO	INTEGER Nullable	Records to force a write (FRCRATIO). 1 through 32767 When the cumulative count of inserted, updated, and deleted records has reached this value, the changed records are forced to storage. Contains the null value is there is no force write ratio.
SELECT_OMIT	SELECTOMIT	VARCHAR(3)	Select/omit setting. NO The file is not a select/omit logical file. YES The file is a select/omit logical file.
PROGRAM_DESCRIBED	PGM_DESC	VARCHAR(3)	Program described file setting. NO The file is not program described. YES The file is program described.
DISTRIBUTED	DIST_FILE	VARCHAR(3)	Distributed file setting. NO The file is not distributed. YES The file is distributed.
FILE_VRM	FILE_VRM	CHAR(6)	File version, release, and modification level. VxRyMz, where x is the version, y the release, and z the modification level. This is the release where the file was created.
EARLIEST_POSSIBLE_RELEASE	MINRLS	CHAR(6) Nullable	Earliest supported version, release, and modification level. New database support used in the file will prevent the file from being saved to a prior version, release, and modification level. The value is formatted as VxRyMz, where x is the version, y the release, and z the modification level. Contains the null value if the value is prior to V2.
ALLOW_NULL_KEYS	ALWNULLK	VARCHAR(3) Nullable	Allow null value key setting (ALWNULL). NO Null value keys are not allowed. YES Null value keys are allowed. Contains the null value if ACCESS_PATH_KEYED is NO.
ALLOW_NULL_DATA	ALWNULLD	VARCHAR(3)	Allow null value data (ALWNULL). NO The file record format(s) do not allow null value fields. YES The file record format(s) allow null value fields.
PRIMARY_KEY	PRIKEY	VARCHAR(3)	Primary key (*PRIKEY). NO The access path for the file is not a primary key. YES The access path for the file is a primary key.
UNIQUE_CONSTRAINT	UNQCST	VARCHAR(3)	Unique constraint. NO The access path for the file is not a unique constraint. YES The access path for the file is a unique constraint.
VOLATILE	VOLATILE	VARCHAR(3)	SQL volatile table setting. NO The file is not an SQL volatile table. YES The file is an SQL volatile table.
KEEP_IN_MEMORY	KEEPINMEM	VARCHAR(3)	The memory preference of the file. NO The file does not have the keep in memory indication set. YES The file has the keep in memory indication set.
MEDIA_PREFERENCE	UNIT	VARCHAR(4)	Preferred storage unit for the file (UNIT). ANY No storage media is preferred. Storage will be allocated from any available storage media. SSD Solid state disk storage media is preferred. Storage may be allocated from solid state disk storage media, if available.
SOURCE_FILE_LIBRARY	SRCLIB	VARCHAR(10) Nullable	Library containing SOURCE_FILE. Contains the null value if a source file was not used.
SOURCE_FILE	SRCFILE	VARCHAR(10) Nullable	Name of the source file containing the DDS used to create the file. Contains the null value if a source file was not used.
SOURCE_FILE_MEMBER	SRCMBR	VARCHAR(10) Nullable	Source file member name within SOURCE_FILE used to create the file. Contains the null value if a source file was not used.
ACCESS_PATH_KEYED	ACCESSPATH	VARCHAR(3)	Whether the file has a keyed sequence access path. NO The file does not have a keyed sequence access path. The file is processed using arrival sequence. YES The file has a keyed sequence access path.
ACCESS_PATH_TYPE	AP_TYPE	VARCHAR(14) Nullable	Access path type. EVI Encoded vector with a 1-, 2-, or 4-byte vector. KEYED FCFO Keyed sequence access path with duplicate keys allowed. Duplicate keys are accessed in first-changed-first-out (FCFO) order. KEYED FIFO Keyed sequence access path with duplicate keys allowed. Duplicate keys are accessed in first-in-first-out (FIFO) order. KEYED LIFO Keyed sequence access path with duplicate keys allowed. Duplicate keys are accessed in last-in-first-out (LIFO) order. KEYED NO ORDER Keyed sequence access path with duplicate keys allowed. No order is guaranteed when accessing duplicate keys. KEYED UNIQUE Keyed sequence access path with no duplicate keys allowed (UNIQUE). Contains the null value if ACCESS_PATH_KEYED is NO.
ACCESS_PATH_MAINTENANCE	MAINT	VARCHAR(6) Nullable	Access path maintenance (MAINT). DLY Delayed maintenance IMMED Immediate maintenance *REBLD Rebuild maintenance Contains the null value if ACCESS_PATH_KEYED is NO.
ACCESS_PATH_SIZE	ACCPTHSIZ	VARCHAR(7) Nullable	Access path size (ACCPTHSIZ). MAX1TB All access paths associated with this file will be allowed to occupy a maximum of 1 terabyte (1 099 511 627 776 bytes) of auxiliary storage MAX4GB All access paths associated with this file will be allowed to occupy a maximum of 4 gigabytes (4 294 966 272 bytes) of auxiliary storage. Contains the null value if ACCESS_PATH_KEYED is NO.
LOGICAL_PAGE_SIZE	PAGESIZE	INTEGER Nullable	Access path page size. -1 Access path is a 4 gigabyte access path 0 System determines page size from the key length of the access path 8 Page size is 8 kilobytes 16 Page size is 16 kilobytes 32 Page size is 32 kilobytes 64 Page size is 64 kilobytes 128 Page size is 128 kilobytes 256 Page size is 256 kilobytes 512 Page size is 512 kilobytes Contains the null value if ACCESS_PATH_KEYED is NO.
FORCE_KEYED_ACCESS_PATH	FRCACCPTH	VARCHAR(3) Nullable	Force keyed access path (FRCACCPTH). NO The access path and changed records are not forced to auxiliary storage when the access path is changed. YES The access path and changed records are forced to auxiliary storage when the access path is changed (*YES). Contains the null value if ACCESS_PATH_KEYED is NO.
ACCESS_PATH_JOURNALED	JOURNALED	VARCHAR(3) Nullable	Access path journaled. NO The access path is not journaled. YES The access path is journaled. Contains the null value if ACCESS_PATH_KEYED is NO.
ACCESS_PATH_RECOVERY	RECOVER	VARCHAR(7) Nullable	Access path recovery (RECOVER). AFTIPL The file access path is built after the IPL is completed. IPL The file access path is built during the IPL. *NO The file access path is built when the file is next opened. Contains the null value if ACCESS_PATH_KEYED is NO.
SRTSEQ_IND	SRTSEQ_IND	INTEGER	Sort sequence table (SRTSEQ) indicators. 1 No sort sequence table is used for the file, and the hexadecimal value of the characters will be used to determine the sort sequence (*HEX). 2 A sort sequence table was specified for the file. 3 No sort sequence table for the file; however, an alternate collating sequence table was specified.
SORT_SEQUENCE_LIBRARY	SRTSEQ_LIB	VARCHAR(10) Nullable	The library containing the sort sequence table or alternate collating sequence table. Can contain the special value *LIBL. Contains the null value if SRTSEQ_IND is 1 or if there is no library.
SORT_SEQUENCE	SRTSEQ	VARCHAR(10) Nullable	The sort sequence table or alternate collating sequence table associated with the file. Contains the null value if SRTSEQ_IND is 1.
LANGUAGE_IDENTIFIER	LANGID	CHAR(3) Nullable	Language identifier (LANGID). Contains the null value if there is no language identifier.
ROUNDING_MODE	DECFLTRND	VARCHAR(8) Nullable	Rounding mode to be used for decimal floating point (DECFLOAT) calculations. CEILING DOWN FLOOR HALFDOWN HALFEVEN HALFUP UP Contains the null value if the file has no decimal floating point fields or this is not an SQL view, SQL index with a derived key expression, SQL materialized query table, or logical file.
DECFLOAT_WARNINGS	DECFLTWRN	VARCHAR(3) Nullable	Indicates whether warnings should be returned from decimal floating point calculations. NO Warnings are not returned. YES Warnings are returned. Contains the null value if the file has no decimal floating point fields or this is not an SQL view, SQL index with a derived key expression, SQL materialized query table, or logical file.
NUMBER_RECORD_FORMATS	FORMATS	INTEGER	Total number of record formats.
FORMAT_LEVEL_ID	FMTLVLID	CHAR(13) Nullable	The record format level ID. Contains the null value if file has more than one format or if no value is available.
FORMAT_NAME	FMT_NAME	VARCHAR(10) Nullable	The name of the record format. Contains the null value if file has more than one format.
RECORD_LENGTH	RCD_LEN	INTEGER Nullable	The length of the record format. Contains the null value if file has more than one format.
NUMBER_FIELDS	FIELDS	INTEGER Nullable	The number of fields in the record format. Contains the null value if file has more than one format.
COMMON_CCSID	CCSID	INTEGER Nullable	The CCSID used when all fields with a character, open, and graphic data type use the same CCSID. Contains the null value if file has more than one format or if all character, open, and graphic fields do not use the same CCSID.
CONTAINS_EXPLICIT_CCSID	EXPLICIT	VARCHAR(3) Nullable	Explicit CCSID setting. NO A CCSID was not specified for any character type fields in the format. YES A CCSID was specified for one or more character type fields in the format. Contains the null value if file has more than one format.
CONTAINS_MULTIPLE_CCSIDS	MULTIPLE	VARCHAR(3)	Multiple CCSID setting. NO The file has only one CCSID for its character type fields or the file has no character type fields. YES The file has more than one CCSID for its character type fields
CONTAINS_UNICODE	UNICODE	VARCHAR(3) Nullable	Format contains UTF-8, UTF-16, or UCS-2 fields. NO The file record format does not contain UTF-8, UTF-16, or UCS-2 fields. YES The file record format contains UTF-8, UTF-16, or UCS-2 fields. Contains the null value if file has more than one format.
CONTAINS_VARYING_LENGTH	VARLEN	VARCHAR(3)	File contains variable length fields (VARLEN). NO The file record format does not contain variable length fields. YES The file record format contains variable length fields.
CONTAINS_DATETIME	DATETIME	VARCHAR(3)	File contains date/time/timestamp fields. NO The file record format does not contain date, time, or timestamp fields. YES The file record format contains date, time, or timestamp fields.
CONTAINS_GRAPHIC	GRAPHIC	VARCHAR(3)	File contains graphic fields. NO The file record format does not contain graphic fields. YES The file record format contains graphic fields.
CONTAINS_LOB	LOB	VARCHAR(3)	File contains large object fields. These are the SQL data types character large object (CLOB), double-byte character large object (DBCLOB), and binary large object (BLOB). NO The file record format does not have a large object field. YES The file record format has a large object field.
CONTAINS_ROWID	ROWID	VARCHAR(3)	File contains ROWID fields. NO The file record format does not have a ROWID column. YES The file record format has a ROWID column.
CONTAINS_UDT	UDT	VARCHAR(3)	File contains user-defined type fields. NO The file record format does not have a user-defined type field. YES The file record format has a user-defined type field.
CONTAINS_DATALINK	DATALINK	VARCHAR(3)	File contains DataLink fields. NO The file record format does not have a DataLink field. YES The file record format has a DataLink field.
CONTAINS_DATALINK_ FILE_LINK_CONTROL	DATALINKFL	VARCHAR(3)	File contains DataLink with FILE LINK CONTROL fields. NO The file record format does not have a DataLink field with FILE LINK CONTROL. YES The file record format has a DataLink field with FILE LINK CONTROL.
CONTAINS_NULL	NULLABLE	VARCHAR(3) Nullable	File contains null capable fields. NO The file record format does not have any null capable fields. YES The file record format has null capable fields. Contains the null value if file has more than one format.
CONTAINS_DEFAULT	DFT	VARCHAR(3) Nullable	The physical file format contains fields with explicit default values. NO The file record format does not have any fields with explicit default values. YES The file record format has fields with explicit default values. Contains the null value if file has more than one format.
CONTAINS_IDENTITY	IDENTITY	VARCHAR(3)	File contains an identity column. NO The file record format does not have an identity column. YES The file record format has an identity column.
CONTAINS_ROW_CHANGE_ TIMESTAMP	ROW_CHANGE	VARCHAR(3)	File contains a row change timestamp column. NO The file record format does not have a row change timestamp column. YES The file record format has a row change timestamp column.
CONTAINS_USER_DEFINED_ FUNCTION	UDF	VARCHAR(3)	File contains a user-defined function. NO The file does not use a user-defined function. YES The file uses a user-defined function.
Values for the following columns are returned when NATIVE_TYPE is PHYSICAL. Otherwise, the columns will contain the null value.
ALLOCATE_STORAGE	ALLOCATE	VARCHAR(3) Nullable	The allocate storage setting (ALLOCATE). NO New members added to the file allow the system to determine storage space that is allocated for the member (ALLOCATE(NO)) YES New members added to the file use the initial number of records to determine storage space that is allocated for the member (ALLOCATE(YES)).
CONTIGUOUS_STORAGE	CONTIG	VARCHAR(3) Nullable	The contiguous storage setting (CONTIG). NO Storage should not be attempted to be allocated contiguously (CONTIG(NO)). YES Storage should be attempted to be allocated contiguously (CONTIG(YES)). Contains the null value if ALLOCATE_STORAGE is NO.
MAXIMUM_DELETED_PERCENTAGE	DLTPCT	INTEGER Nullable	Maximum percentage of deleted records allowed (DLTPCT). 1 to 100 The threshold percentage of deleted records a member can contain before a message is sent to the history log. See Deleted records for additional information. Contains the null value if the number of deleted records is not checked when the member is closed (*NONE).
INITIAL_RECORDS	SIZE_INIT	INTEGER Nullable	Initial number of records (SIZE). This is the number of records that can be inserted before an automatic extension occurs. Contains the null value if the number of records that can be inserted into each member is not limited by the user. The system determines the maximum member size (*NOMAX).
INCREMENT_RECORDS	SIZE_INCR	INTEGER Nullable	Increment number of records (SIZE). This is the maximum number of records that can be inserted into the member after an automatic extension occurs. Contains the null value when INITIAL_RECORDS is null.
MAXIMUM_INCREMENTS	SIZE_MAX	INTEGER Nullable	Maximum number of increments (SIZE). This is the maximum number of automatic extensions that can be made to the member. Contains the null value when INITIAL_RECORDS is 0.
REUSE_DELETED_RECORDS	REUSEDLT	VARCHAR(3) Nullable	Reuse deleted records (REUSEDLT). NO Deleted records are not reused on subsequent writes or inserts. YES Deleted records can be reused on subsequent writes or inserts.
MATERIALIZED_QUERY_TABLE	MQT	VARCHAR(3) Nullable	SQL materialized query table setting. NO This is not an SQL materialized query table. YES This is an SQL materialized query table.
PARTITIONED_TABLE	PARTITION	VARCHAR(3) Nullable	Partitioned table setting. NO This is not a partitioned table. YES This is a partitioned table.
ROW_AND_COLUMN_ACCESS_ CONTROL	RCAC	VARCHAR(3) Nullable	Row and column access control setting. NO Access controls are not defined for the file. YES Access controls are defined for the file. The QSYS2.SYSCONTROLS view contains detailed information about row and column access controls.
Values for the following columns are returned when NATIVE_TYPE is LOGICAL. Otherwise, the columns will contain the null value.
TOTAL_SELECT_OMIT	TOTAL_SO	INTEGER Nullable	Total number of select/omit statements for all record formats.
FMTSLR_LIBRARY	FMTSLR_LIB	VARCHAR(10) Nullable	Record format selector program library (FMTSLR) Contains the null value if there is no record format selector program.
FMTSLR_PROGRAM	FMTSLR_PGM	VARCHAR(10) Nullable	Record format selector program (FMTSLR) Contains the null value if there is no record format selector program.
IS_JOIN_LOGICAL	JFILE	VARCHAR(3) Nullable	Join logical file setting (JFILE). NO The file is not a join logical file. YES The file is a join logical file.
DYNAMIC_SELECTION	DYNSLT	VARCHAR(3) Nullable	Dynamic selection setting (DYNSLT). NO The selection and omission tests specified for the file are done when the access path is updated. YES The selection and omission tests specified for the file are done when the file is read.
WITH_CHECK_OPTION	CHECK	VARCHAR(8) Nullable	With check option. CASCADED The cascaded check option was specified. NO No check option was specified or this is not an SQL view. LOCAL The local check option was specified.
PHYSICAL_LOB	PF_LOB	VARCHAR(3) Nullable	Whether this logical file has no large object fields, but the based-on physical file has a large object field. NO The logical file and based-on physical file either both have large object fields or both do not. YES The logical file has no large object fields, but the based-on physical file has a large object field.
PHYSICAL_DATALINK	PF_DATALNK	VARCHAR(3) Nullable	Whether this logical file has no DataLink fields, but the based-on physical file has a DataLink field. NO The logical file and based-on physical file either both have DataLink fields or both do not. YES The logical file has no DataLink fields, but the based-on physical file has a DataLink field.
INDEX_COLUMN_IS_EXPRESSION	IXEXP	VARCHAR(3) Nullable	If the SQL index key column is an expression. NO The key column is not an expression or this is not an SQL index. YES The key column is an expression.
INDEX_EXPRESSION_HAS_UDF	IXEXPUDF	VARCHAR(3) Nullable	If the SQL index key column is an expression and the expression contains a user-defined function (UDF). NO The key column is not an expression or the expression does not contain a user-defined function, or this is not an SQL index. YES The key column is an expression and the expression contains a UDF.
INDEX_HAS_SEARCH_CONDITION	SPARSE	VARCHAR(3) Nullable	If the index has a search condition. NO The index does not have a search condition, or this is not an SQL index YES The index has a search condition.
INDEX_SEARCH_CONDITION_ HAS_UDF	SPARSE_UDF	VARCHAR(3) Nullable	If the index search condition contains a user-defined function. NO The index is not sparse or does not contain a user-defined function, or this is not an SQL index YES The index is sparse and the search condition contains a UDF.

Examples

Examine all the files in APPLIB1.

 SELECT * FROM QSYS2.SYSFILES WHERE LIB_NAME = 'APPLIB1';

List the files that native logical files in APPLIB1 are based on.

WITH BASED_ONS (LF_NAME, BASED_ON_NAMES) AS (
    SELECT TABLE_NAME, BASED_ON_FILES
      FROM QSYS2.SYSFILES 
      WHERE SYSTEM_TABLE_SCHEMA = 'APPLIB1' AND
            NATIVE_TYPE = 'LOGICAL' AND
            SQL_OBJECT_TYPE IS NULL  
  )
  SELECT DISTINCT LF_NAME, LIBRARY AS BASED_ON_LIBRARY, FILE AS BASED_ON_FILE 
    FROM BASED_ONS, JSON_TABLE(
           BASED_ON_NAMES,
           'lax $.BASED_ON_FILES'
           COLUMNS(
             LIBRARY CHAR(10), FILE CHAR(10)
           ))
  order by 1, 2, 3;